#!/usr/bin/env python
# _*_coding:utf-8_*_
'''
 * Created on 2016/12/5 22:31.
 * @author: Chinge_Yang.
'''

import os


def file_to_data(table):
    """
    # 读文件成数据
    :param  表名
    :return:    表结构(列表），数据（列表）
    """
    n = 0
    data_list = []
    with open(table, "r", encoding="utf-8") as f:
        for line in f:
            line = line.strip()  # 去除换行符
            if n == 0:
                # ["staff_id","name","age","phone","dept","enroll_date"]
                struct_list = line.split(",")  # 第一行
            else:
                line_list = line.split(",")
                data_list.append(line_list)  # 其它行
            n += 1
    return struct_list, data_list


def data_to_file(struct_list, data_list, table):
    """
    # 把数据写入文件
    :return:
    """
    with open(table, "w", encoding="utf-8") as f:
        f.write(','.join(struct_list) + "\n")
        for sub_list in data_list:
            f.write(",".join(sub_list) + "\n")
    print("Done!")


def print_help(action):
    print("The grammar of the {} support likes:\n".format(action))
    if action == "select":
        print("\tselect * from staff_table;")
        print("\tselect name,age from staff_table where age > 22;")
        print("\tselect * from staff_table where dept = \"IT\";")
        print("\tselect * from staff_table where enroll_date like \"2013\";")
    elif action == "add":
        print("\tadd [Alex Li,22,13651054608,IT,2013-04-01];")
    elif action == "update":
        print("\tupdate staff_table set dept = \"Market\" where dept = \"IT\";")
    elif action == "delete":
        print("\tdelete 5;")


def get_column_number(column, struct_list):
    """
    # 获取列位置
    :param column:  列名，此处只实现支持一个
    :return:
    """
    column_number = struct_list.index(column)  # 结果为数字
    return column_number


def input_sql():
    # 获取输入SQL
    exit_flag = False
    while exit_flag is not True:
        print("-".center(60, "-"))
        print("Tip: Input 【help [select/update/add/delete]】 to get help.")
        print("-".center(60, "-"))
        sql = input("Please input SQL:").strip().strip(";")
        if sql.startswith('help'):
            action = sql.split(" ")[1]
            print_help(action)
            continue
        if sql == "q" or sql == "quit":
            exit(" Bye Bye ".center(60, "#"))
        exit_flag = True
    return sql


def sql_to_list(sql):
    # 处理双引号中包含空格时，列表下标保持不变
    tmp_sql = sql.split(' ')
    sql_list = []
    tmp = ''
    flag = 1    # 列表添加元素标识
    for l in tmp_sql:
        if l.startswith('"') and l.endswith('"'):
            flag = 1
        elif l.startswith('"') and (not l.endswith('"')):
            flag = 0
            tmp = l + ' '
        elif (not l.startswith('"')) and (not l.endswith('"')):
            if flag == 0:
                l += ' '
                tmp += l
            else:
                flag = 1
        elif (not l.startswith('"')) and l.endswith('"'):
            if flag == 0:
                tmp += l
                flag = 1
                sql_list.append(tmp)
                continue

        if flag == 1:
            sql_list.append(l)
    return sql_list


def check_table(table, c_table):
    """
    # 判断表是否存在
    :param table: 表名
    :return:
    """
    if table != c_table:
        print("Your input table \033[31m{}\033[0m is not exists,"
              "please check!".format(c_table))
        print("#".center(60, "#"))
        return True  # 标记给continue_flag


def check_quotes(str):
    """
    :param str: 需要处理的字符串
    :return: 返回无符号的字符串
    """
    if '"' in str:
        str = str.strip('"')
    return str


def auto_increment_id(data_list):
    # 记录自增长ID
    file = "auto_increment_id"
    max_staff_id = int(data_list[-1][0])    # 表中最大的staff_id
    id = 0  # 初始化
    if os.path.exists(file):    # 自增id文件存在时
        with open(file, "r+") as f:
            for line in f:
                id = int(line)
    if max_staff_id <= id:
        new_staff_id = id + 1
    else:
        new_staff_id = max_staff_id + 1
    with open(file, "w+") as f:
        f.write(str(new_staff_id))
    return new_staff_id


def analyze(sql):
    input_info = sql_to_list(sql)
    # return input_info
    action = input_info[0]  # 查：select；增：add；改：update；删：delete
    return action


def select(sql, struct_list, data_list, table):  # select sql语法分析
    input_info = sql_to_list(sql)
    select_column = input_info[1].split(",")  # 可能有“,”号
    try:
        table_name = input_info[3]
    except Exception as e:
        print("Your input is error!")
        return True
    continue_flag = check_table(table, table_name)
    if continue_flag is True:
        return True

    all_column = False
    all_line = False
    # 查询列，* 或者指定列
    if "*" in select_column:  # 打印所有列
        all_column = True
    else:
        column_numbers = []  # 输出列的数字列表
        for s_column in select_column:
            s_number = get_column_number(s_column, struct_list)
            column_numbers.append(s_number)

    if "where" in sql:
        # 由于双引号问题，此处加上双引号
        where_flag = input_info[4]  # where
        condition_column = input_info[5]  # 条件字段
        condition_str = input_info[6]  # 限制条件关键字，支持“=”，“>=”，“like”等
        condition_value = input_info[7]  # 条件参数
        condition_value = check_quotes(condition_value) # 去除双引号
        column_number = get_column_number(condition_column, struct_list)  # 列位置

        match_data_list = []  # 匹配出来的结果，列表格式，
        # 查询行,有like、>=、= 等
        if where_flag == "where":  # 有where
            if condition_str == "like":
                # like
                for line in data_list:  # line也是列表
                    if condition_value in line[column_number]:  # 匹配like
                        match_data_list.append(line)
            elif condition_str == "=":
                for line in data_list:
                    if line[column_number] == condition_value:
                        match_data_list.append(line)
            elif condition_str == ">":
                for line in data_list:
                    if line[column_number] > condition_value:
                        match_data_list.append(line)
            elif condition_str == ">=":
                for line in data_list:
                    if line[column_number] >= condition_value:
                        match_data_list.append(line)
            elif condition_str == "<":
                for line in data_list:
                    if line[column_number] < condition_value:
                        match_data_list.append(line)
            elif condition_str == "<=":
                for line in data_list:
                    if line[column_number] <= condition_value:
                        match_data_list.append(line)
            else:
                print("Not support {}".format(condition_str))
    else:  # 无where，取所有行
        all_line = True
        match_data_list = data_list

    # 打印结果
    print("The select result:")
    print("#".center(60, "#"))
    print("\033[32m{}\033[0m rows in set".format(len(match_data_list)))
    if all_column is True:
        print("{:>8} {:>8} {:>8} {:>8} {:>8} {:>8}".format(*struct_list))
        for line in match_data_list:
            print("{:>8} {:>8} {:>8} {:>8} {:>8} {:>8}".format(*line))
    else:
        len_num = len(select_column)
        format_str = '{:>8} ' * len_num
        print(format_str.format(*select_column))
        for line in match_data_list:
            line_list = []
            for s in column_numbers:
                line_list.append(line[s])
            print(format_str.format(*line_list))
    print("#".center(60, "#"))

def add(sql, struct_list, data_list, table):
    # sql: Alex Li,22,13651054608,IT,2013-04-01
    input_info = sql.strip().strip("add [").strip("]")
    add_list = input_info.split(",")
    phone = add_list[2]
    phone_exist = False
    for d_list in data_list:
        if phone == d_list[3]:
            phone_exist = True
    if phone_exist is True:
        print("Thone phone is exist,can't add.")
        return True
    else:
        new_staff_id = auto_increment_id(data_list)
        add_list.insert(0, str(new_staff_id))
        data_list.append(add_list)
    data_to_file(struct_list, data_list, table)


def delete(sql, struct_list, data_list, table):
    # delete 5;
    delete_flag = False
    input_info = sql_to_list(sql)
    staff_id = input_info[1]
    for d_list in data_list:
        if staff_id == d_list[0]:
            delete_flag = True
            data_list.remove(d_list)

    if delete_flag is not True:
        print("The staff_id is not exist,can't delete.")
    else:
        data_to_file(struct_list, data_list, table)


def update(sql, struct_list, data_list, table):
    # update staff_table set dept = "Market" where dept = "IT";   只允许修改age,phone,dept,enroll_date
    input_info = sql_to_list(sql)
    table_name = input_info[1]
    set_flag = input_info[2]
    modify_column = input_info[3]  # 修改的字段
    equal_flag = input_info[4]  # 等于符号
    modify_value = input_info[5]  # 修改后的值
    modify_value = check_quotes(modify_value) # 去除双引号
    where_flag = input_info[6]
    condition_column = input_info[7]  # 条件字段
    condition_str = input_info[8]  # 限制条件关键字，只支持“=”
    condition_value = input_info[9]  # 条件参数
    condition_value = check_quotes(condition_value) # 去除双引号
    modify_column_number = get_column_number(modify_column, struct_list)  # 列位置
    condition_column_number = get_column_number(condition_column, struct_list)  # 列位置

    modify_flag = False
    continue_flag = check_table(table, table_name)
    if continue_flag is True:
        return True
    if set_flag == "set" and equal_flag == "=" and where_flag == "where" and condition_str == "=":
        phone_exist = False
        phone = modify_value
        for d_list in data_list:
            if phone == d_list[3]:
                phone_exist = True
        if phone_exist is True:     # phone唯一性
            print("Thone phone is exist,can't update.")
            return True
        for d_list in data_list:
            # 由于双引号问题，此处加上双引号
            if d_list[condition_column_number] == condition_value:
                d_list[modify_column_number] = modify_value
                modify_flag = True
        if modify_flag is not True:
            print("Not match any record!")
        else:
            data_to_file(struct_list, data_list, table)
    else:
        print("Your input is error!")


def main():
    exit_flag = False
    table = "staff_table"
    while exit_flag is not True:
        sql = input_sql()
        action = analyze(sql)
        struct_list, data_list = file_to_data(table)
        if action == "select":
            continue_flag = select(sql, struct_list, data_list, table)
            if continue_flag is True:
                continue    # 重新循环
        elif action == "add":
            continue_flag = add(sql, struct_list, data_list, table)
            if continue_flag is True:
                continue    # 重新循环
        elif action == "update":
            continue_flag = update(sql, struct_list, data_list, table)
            if continue_flag is True:
                continue
        elif action == "delete":
            delete(sql, struct_list, data_list, table)
        else:
            print("Your input error!")


if __name__ == '__main__':
    main()
